CREATE VIEW [dbo].[vSoaGroupMemberDetailSubscription]
WITH SCHEMABINDING
AS
    SELECT  CAST([p1].[PRODUCT_CODE] + ':' + [s].[ID] AS VARCHAR(50)) AS [GroupMemberDetailId],
            CAST([p1].[PRODUCT_CODE] + ':' + [s].[ID] AS VARCHAR(50)) AS [GroupMemberId],
            [s].[ID] AS [PartyId],
            [s].[BEGIN_DATE] AS [EffectiveDate],
            [s].[PAID_THRU] AS [ExpirationDate],
            CAST(NULL AS VARCHAR(30)) AS [GroupRoleId],
            CAST(NULL AS VARCHAR(100)) AS [RoleName],
            CAST(NULL AS VARCHAR(100)) AS [RoleDescription],
            CAST(NULL AS INT) AS [RolePriority],
            CAST(NULL AS VARCHAR(100)) AS [RoleReciprocalName],
            CAST(NULL AS VARCHAR(100)) AS [RoleReciprocalDescription],
            CAST(NULL AS VARCHAR(100)) AS [Title],
            CAST(NULL AS VARCHAR(6000)) AS [Note]
      FROM  [dbo].[Subscriptions] AS [s]
            INNER JOIN [dbo].[Product] AS [p1] ON [s].[PRODUCT_CODE] = [p1].[PRODUCT_CODE]
     WHERE  ( [p1].[PROD_TYPE] IN ( 'DUES', 'SUB', 'SEC', 'CHAPT', 'VOL',
                                    'MISC' ) )


GO
CREATE UNIQUE CLUSTERED INDEX [PK_vSoaGroupMemberDetailSubscription] ON [dbo].[vSoaGroupMemberDetailSubscription] ([GroupMemberDetailId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaGroupMemberDetailSubscription_GroupMemberId] ON [dbo].[vSoaGroupMemberDetailSubscription] ([GroupMemberId]) ON [PRIMARY]
GO